<?php

class Database {
	private static $databases = array(); // Static collection of all databases
		
	public $id			= '';		// Unique html/url safe ID
	public $name		= '';		// Database name
	public $tables		= array();	// An array of Table objects
	public $hasVirtual	= false;	// Do our meta tables exist?
	
	/**
	 * Transpose an MySQL database into an object representation
	 * @return 
	 * @param $dbname Object
	 */
	function __construct($dbName){
		$this->name	= $dbName;
		$this->id	= $dbName; // The name of the database will serve as the ID
		
		MySQL::selectDatabase($this->name) or HTTP::mySQLError();
		$tableNames = MySQL::tableList($this->name);
		if ( $tableNames === false ) HTTP::mySQLError();
		// Check for meta tables
		$this->hasVirtual = in_array('zReports', $tableNames);
		// Remove any hidden tables
		$tableNames = array_diff($tableNames, Config::$hiddenTables);
		// Populate our table array
		foreach ( $tableNames as $tableName ){
			$table = new Table($this, $tableName);
			$this->tables[$table->id] = $table;
		}
	}
	
	/**
	 * CREATE a database with the given name and return
	 * it's object representation
	 * 
	 * @return Database Created database
	 * @param $dbname String Name of new database
	 */
	static function create($dbName){
		MySQL::dbCreate($dbName) or HTTP::mySQLError();
		return new Database($dbName);
	}
	
	/**
	 * RETRIEVE a specific database object or an array 
	 * of all of them if no $id is given
	 * 
	 * @return Mixed
	 * @param $id String[optional] ID of database
	 */
	static function retrieve($id=''){
		
		// Populate our static array of database objects if it's empty
		if ( empty(Database::$databases) ){
			$dbNames = MySQL::dbList();
			// Remove our hidden dbs from our list
			$dbNames = array_diff($dbNames, Config::$hiddenDBs);			
			foreach ( $dbNames as $dbName ){
				Database::$databases[] = new Database($dbName);
			}
		}
		
		// Return the requested database
		if ( $id != '' ){
			foreach ( Database::$databases as $db ){
				if ( $db->id == $id ){
					MySQL::selectDatabase($db->name);
					return $db;	
				}
			}
		}
		// Return all the databases
		return Database::$databases;
	}
	
	/**
	 * UPDATEing a database is next to impossible after it's been
	 * created. Even a simple rename is out of the question.
	 */
	
	/**
	 * DELETE this database completely
	 * 
	 * @return 
	 */
	function delete(){
		MySQL::dbDelete($this->name) or HTTP::mySQLError();
		return;
	}
}

class Table {
	public $id = '';			// Table id
	public $db;					// Inverse to Database object
	public $name = '';			// Table name
	public $singularName = '';	// Table name - singular version
	public $fields = array();	// An array of Field objects
	public $parents = array();	// Record any references we have
	
	/**
	 * Transpose a table found in SQL in the specified database
	 * and attach it to that database
	 * 
	 * @param $db Database
	 * @param $tableName String
	 */
	function __construct($db, $tableName){
		$this->db = $db;
		$this->name = $tableName;
		$this->id = $db->id.'.'.$this->name;
		$this->singularName = Inflect::singularize($this->name);
		
		// Populate our table fields
		foreach ( Field::retrieve($this) as $field ){
			$this->fields[$field->id] = $field;
			if ( $field->reference != '' ) $this->parents[] = $field->reference;
		}
		
		// Populate our virtual fields
		foreach ( VirtualField::retrieve($this) as $vField ){
			if ( $vField->afterField ){
				$insert[$vField->id] = $vField;
				if ( $vField->afterField == 'first' ) array_splice($this->fields, 0, 0, $insert);
				$i = 0;
				foreach ( $this->fields as $field ){
					$i++;
					if ( $field->name == $vField->afterField ) array_splice($this->fields, $i, 0, $insert);
				}
			} else {
				$this->fields[$vField->id] = $vField;
			}
		}
	}
	
	/**
	 * CREATE a new table in our database and make sure
	 * that the object relationships all stand
	 * 
	 * @return Table The newly created table
	 * @param $db Database The database to create the table on
	 * @param $tableName String The name of the table
	 */
	static function create($db, $tableName){
		MySQL::tableCreate($tableName) or HTTP::mySQLError();
		$table = new Table($db, $tableName);
		$db->tables[$table->id] = $table;
		return $table;
	}
	
	/**
	 * RETRIEVE a Table object from the given database or
	 * return a list of all tables in the database
	 * 
	 * @return Mixed
	 * @param $db Database
	 * @param $id String[optional]
	 */
	static function retrieve($id=''){
		$tables = array();
		foreach ( Database::retrieve() as $db ){
			if ( $id != '' and isset($db->tables[$id]) ){
				MySQL::selectDatabase($db->name);
				return $db->tables[$id];	
			}
			//array_push($tables, $db->tables); // wtf doesn't this work?
			foreach ( $db->tables as $table ){
				$tables[] = $table;
			}
		}
		return $tables;
	}
	
	/**
	 * UPDATE the table = although basically just a rename. All
	 * other alterations happen at a Field level
	 * 
	 * @param $tableName String New name for table
	 */
	function update($tableName){
		MySQL::tableRename($this->name, $tableName) or HTTP::mySQLError();
		$this->name = $tableName;
	}
	
	/**
	 * DELETE this table
	 */
	function delete(){
		//MySQL::selectDatabase($this->db->name) or HTTP::mySQLError();
		MySQL::tableDelete($this->name) or HTTP::mySQLError();
		unset($this->db->tables[$this->id]);
	}
	
	function findField($id){
		foreach ( $this->fields as $field ){
			if ( $field->id == $id ) return $field;
		}
		return false;
	}
	
	function reorderFields($fieldOrder){
		/**
		 * STAGE 1: Reorder our $this->fields as per the adjustments by the user
		 */
		foreach ( $fieldOrder as $id => $afterID ){
			// Find our desired field and create our insertable array
			$thisField = $this->findField($id);
			$insert[$id] = $thisField;
			
			// Remove from current fields
			unset($this->fields[$id]);
			
			// Insert at start if required
			if ( $afterID == 'first' ){
				array_splice($this->fields, 0, 0, $insert);
				continue;	
			}
			
			// Find target field
			$i = 0;
			foreach ( $this->fields as $field ){
				$i++;
				// If it is the target field, insert after
				if ( $field->id == $afterID ) array_splice($this->fields, $i, 0, $insert );
			}
		}
		
		/**
		 * STAGE 2: Apply the newly ordered $this->fields to our SQL
		 */
		// Split our array into two
		$sqlFields = array();
		foreach ( $this->fields as $field ){
			if ( $field->isVirtual ) continue;
			$sqlFields[] = $field;
		}
		
		// Order our real sql fields
		foreach ( $sqlFields as $field ){
			// Skip our first step
			if ( isset($lastField) ) {
				// Otherwise change our column order
				$field->update($field->name, $field->type, $lastField->name);
			}
			$lastField = $field;
		}
		
		// Attach our virtual fields to their previous fields
		$lastField = null;
		foreach ( $this->fields as $field ){
			// Skip our first step
			if ( isset($lastField) ) {
				if ( $field->isVirtual ) {
					// If we're virtual update the virtual field record
					$field->update($field->name, $field->sql, $lastField->name);
				}
			}
			$lastField = $field;
		}
	}
	
	function afterFields($currentField=null){
		$opts = array();
		$opts['first'] = '[First]';
		foreach ( $this->fields as $field ){
			if ( $field == $currentField ) continue;
			$opts[$field->id] = $field->displayName;
		}
		return $opts;
	}
	
	function recordCount(){
		return MySQL::tableRecordCount($this->name);
	}
	
	function records(){
		//MySQL::selectDatabase($this->db->name) or HTTP::mySQLError();
		$vFields = array();
		foreach ( $this->fields as $field ){
			if ( $field->isVirtual ) $vFields[] = array('name' => $field->displayName, 'sql' => $field->sql);
		}
		$records = MySQL::tableRecords($this->name, array('extraFields' => $vFields, 'join' => $this->joinClause(), 'fromAlias' => $this->singularName));
		if ( $records === false ) HTTP::mySQLError();
		return $records;
	}
	
	/**
	 * Determine which tables we refer to
	 * @return Array An array of Table objects
	 */
	function parents(){
		$parents = array();
		foreach ( $this->parents as $parent ){
			foreach ( Table::retrieve() as $table ){
				if ( $table->name == $parent ) $parents[] = $table;
			}
		}
		return $parents;
	}
	
	/**
	 * Determine which tables refer to us
	 * @return Array An array of table objects
	 */
	function children(){
		$children = array();
		foreach ( Table::retrieve() as $table ){
			if ( $table->db != $this->db ) continue;
			if ( in_array($this->name, $table->parents) ) $children[] = $table;
		}
		return $children;
	}
	
	private function familyOptions($family){
		$options = array();
		foreach ( $family as $member ){
			$table = $member['table'];
			$name = join('.', $member['ancestry']);
			$displayName = join('.', array_splice($member['ancestry'], 1));
			$first = true;
			foreach ( $table->fields as $field ){
				if ( $field->reference or $field->isVirtual ) continue;
				if ( $first )	$options[$name.'.'.$field->name] = $displayName;
				else			$options[$name.'.'.$field->name] = $displayName.'.'.$field->name;
				$first = false;
			}
		}
		return $options;
	}
	
	function summaryOptions(){
		return $this->familyOptions($this->descendants());
	}
	
	function parentOptions(){
		return $this->familyOptions($this->ancestors());
	}
	
	function ancestors($ancestry=array()){
		if ( empty($ancestry) ) $ancestry[] = $this->singularName;
		// Iterate through all of the tables we reference
		$ancestors = array();
		foreach ( $this->parents() as $parent ){
			// Check we're not going to have a circular reference
			if ( in_array($parent->singularName, $ancestry) ) continue;
			// Push us onto the stack
			$ancestry[] = $parent->singularName;
			Utility::debug("$this->name belong_to $parent->name ==> ".join('.', $ancestry));
			$ancestors[] = array(
				'table'		=> $parent,
				'ancestry'	=> $ancestry,
			);
			// Recurse up the family tree
			$ancestors = array_merge($ancestors, $parent->ancestors($ancestry));
			array_pop($ancestry);
		}
		return $ancestors;
	}
	
	function descendants($ancestry=array()){
		if ( empty($ancestry) ) $ancestry[] = $this->singularName;
		$descendants = array();
		foreach ( $this->children() as $child ){
			if ( in_array($child->singularName, $ancestry) ) continue;
			$ancestry[] = $child->name;
			Utility::debug("$this->name have_many $child->name ==> ".join('.', $ancestry));
			$descendants[] = array(
				'table'		=> $child,
				'ancestry'	=> $ancestry,
			);
			$descendants = array_merge($descendants, $child->descendants($ancestry));
			array_pop($ancestry);
		}
		return $descendants;
	}
	
	function joinClause() {
		$myJoins = array();
		
		// Iterate through all of our ancestors
		// and build our join clause
		foreach ( $this->ancestors() as $ancestor ){
			$name		= join('.', $ancestor['ancestry']);
			$parentName	= join('.', array_slice($ancestor['ancestry'], 0, count($ancestor['ancestry'])-1));
			$myJoins[] = "LEFT JOIN `{$ancestor['table']->name}` AS `$name` ON `$name`.`id` = `$parentName`.`{$ancestor['table']->singularName}_id`";
		}
		
		// Iterate through all the tables that reference us
		// and add to our join clause
		foreach ( $this->descendants() as $descendant ){
			$name		= join('.', $descendant['ancestry']);
			$childName	= join('.', array_slice($descendant['ancestry'], 0, count($descendant['ancestry'])-1));
			$fieldName	= Inflect::singularize($descendant['ancestry'][count($descendant['ancestry'])-2]);
			$myJoins[] = "LEFT JOIN `{$descendant['table']->name}` AS `$name` ON `$childName`.`id` = `$name`.`{$fieldName}_id`";
		}
		
		return join("\n", $myJoins);
	}
}

class Field {
	public $id;
	public $table;
	public $name;
	public $displayName;
	public $type;
	public $isVirtual = false;
	public $reference;
	
	/**
	 * Initialise an object representation of the SQL field
	 * 
	 * @param $table Object
	 * @param $fieldName Object
	 * @param $sqlType Object
	 */
	function __construct($table, $sqlRecord){
		$this->table = $table;
		$this->name = $sqlRecord['Field'];
		$this->type = Types::zanzo($sqlRecord['Type']);
		$this->displayName = $this->name;
		$this->id = $table->id.'.'.$this->name;
		
		if ( Field::isReference($this->name, $referenceTo) ){
			$this->type = 'Reference';
			$this->displayName = Inflect::singularize($referenceTo);
			$this->reference = $referenceTo;
		}
	}
	
	/**
	 * CREATE a new field.
	 * 
	 * @return Field
	 * @param $table Object
	 * @param $fieldName Object
	 * @param $fieldType Object
	 * @param $afterField Object
	 */
	static function create($table, $fieldName, $fieldType, $afterField=''){
		// Translate our $fieldType to something SQLesque
		$sqlType = Types::$sql[$fieldType];
		$fieldName = $fieldType == 'Reference' && strpos($fieldName, '_id') === false ? $fieldName.'_id' : $fieldName;
		// Create our field in SQL
		MySQL::tableAddField($table->name, $fieldName, $sqlType, $afterField) or HTTP::mySQLError();
		// Get the object representation of our field
		$sqlRecord = array(
			'Field'	=> $fieldName,
			'Type'	=> $fieldType
		);
		$field = new Field($table, $sqlRecord);
		$table->fields[$field->id] = $field;
		return $field;
	}
	
	/**
	 * RETRIEVE a specific field
	 * 
	 * @return Field
	 * @param $table Object
	 * @param $id Object[optional]
	 */
	static function retrieve($table, $id=''){
		
		$fields = array();
		foreach ( MySQL::tableFields($table->name) as $sqlField ){
			if ($sqlField['Field'] == 'id') continue;
			$field = new Field($table, $sqlField);
			$fields[$field->id] = $field;
		}
		
		if ( $id != '' ){
			return $fields[$id];
		}
		return $fields;
	}
	
	/**
	 * UPDATE our field. Allows for renaming, reordering and changing of types
	 * 
	 * @param $fieldName String New name for field
	 * @param $fieldType String Zanzo type for field
	 * @param $afterField String[optional] Name of field to position after
	 */
	function update($fieldName, $fieldType, $afterField=''){
		// Translate our $fieldType to something SQLesque
		$sqlType = Types::$sql[$fieldType];
		$fieldName = $fieldType == 'Reference' && strpos($fieldName, '_id') === false ? $fieldName.'_id' : $fieldName;
		if ( $sqlType == '' ) return;
		MySQL::tableAlterField($this->table->name, $this->name, $fieldName, $sqlType, $afterField) or HTTP::mySQLError();
		$this->name = $fieldName;
	}
	
	/**
	 * DELETE the field completely
	 * @return 
	 */
	function delete(){
		MySQL::tableDeleteField($this->table->name, $this->name) or HTTP::mySQLError();
		unset($this->table->fields[$this->id]);
	}
	
	function referenceOpts(){
		//MySQL::selectDatabase($this->table->db->name);
		$rows = MySQL::tableRecords($this->reference) or HTTP::mySQLError();
		$options = array();
		foreach ( $rows as $row ){
			$options[$row['id']] = $row[1];
		}
		return $options;
	}
	
	/**
	 * Determine if a field is a reference to another table by the naming
	 * convention of the fieldname.
	 * 
	 * @return Boolean True if the fieldname is a reference
	 * @param $fieldname String Name of field
	 * @param $referenceTo String The table name being referenced
	 */
	static function isReference($fieldname, &$referenceTo){
		$referenceTo = null;
		if ( strpos($fieldname, '_id') !== false ){
			$referenceTo = Inflect::pluralize(str_replace('_id', '', $fieldname));
			return true;
		}
		return false;
	}
}

class VirtualField extends Field {
	public $sql;
	public $afterField;
	
	function __construct($table, $sqlRecord){
		$this->table		= $table;
		$this->id			= $sqlRecord['id'];
		$this->name			= $sqlRecord['name'];
		$this->displayName	= $sqlRecord['name'];
		$this->sql			= $sqlRecord['sql'];
		$this->type			= 'Virtual';
		$this->isVirtual	= true;
		if ( isset($sqlRecord['insertAfter']) ) $this->afterField = $sqlRecord['insertAfter'];
	}
	
	static function create($table, $vFieldName, $vFieldSQL){
		MySQL::selectDatabase($table->db->name) or HTTP::mySQLError();
		if ( ! $table->db->hasVirtual ) VirtualField::setup();
		$reportID = Report::safeCreate($table->db, $table->name) or HTTP::mySQLError();
		$sqlField = array(
			'name'			=> $vFieldName,
			'zReport_id'	=> $reportID,
			'sql'			=> $vFieldSQL
		);
		$sqlField['id'] = MySQL::tableCreateRecord('zFields', $sqlField) or HTTP::mySQLError();
		$vField = new VirtualField($table, $sqlField);
		$table->fields[$vField->id] = $vField;
	}
	
	static function retrieve($table, $id=''){
		if ( ! $table->db->hasVirtual ) return array();
		$rows = MySQL::tableRecords(
			"zFields",
			array(
				'join' => 'LEFT JOIN `zReports` AS `zReport` ON `zReport`.id = `zFields`.`zReport_id`',
				'where' => sprintf('`zReport`.`name` = "%s"', $table->name)
			)
		);
		if ( $rows === false ) HTTP::mySQLError();

		$vFields = array();
		foreach ( $rows as $row ){
			$vField = new VirtualField($table, $row);
			$vFields[$vField->id] = $vField;
		}
		if ( $id != '' ){
			return $vFields[$id];
		}
		return $vFields;
		
	}
	
	function update($name, $sql, $afterField=''){
		$sqlRecord = array(
			'name'			=> $name,
			'sql'			=> $sql,
			'insertAfter'	=> $afterField
		);
		MySQL::tableUpdateRecord('zFields', $this->id, $sqlRecord) or HTTP::mySQLError();
		$this->name = $name;
		$this->sql = $sql;
		$this->afterField = $afterField;
		return $this;
	}
	
	function delete(){
		MySQL::tableDeleteRecord('zFields', $this->id) or HTTP::mySQLError();
		unset($this->table->fields[$this->id]);
	}
	
	static function setup(){
		MySQL::tableCreate('zFields') or HTTP::mySQLError();
		MySQL::tableAddField('zFields', 'name', Types::$sql['Short String']) or HTTP::mySQLError();
		MySQL::tableAddField('zFields', 'sql', Types::$sql['Long String']) or HTTP::mySQLError();
		MySQL::tableAddField('zFields', 'insertAfter', Types::$sql['Short String']) or HTTP::mySQLError();
		MySQL::tableAddField('zFields', 'zReport_id', Types::$sql['Reference']) or HTTP::mySQLError();
	}
}

class Types {
	public static $sql = array(
		'Short String'	=> 'varchar(30)',
		'Long String'	=> 'varchar(100)',
		'Number'		=> 'int(10)',
		'Decimal'		=> 'decimal(10,4)',
		'Boolean'		=> "enum('N','Y')",
		'Date/Time'		=> "datetime",
		'Reference'		=> "int(11)",
	);
	
	static function zanzo($sqlType){
		$zTypes = array_flip(Types::$sql);
		$zType = isset($zTypes[$sqlType]) ? $zTypes[$sqlType]: 'Unknown';
		return $zType;
	}
	
	public static $summaryTypes = array(
		'Count'		=> 'COUNT',
		'Sum'		=> 'SUM',
		'Maximum'	=> 'MAX',
		'Minimum'	=> 'MIN',
		'Average'	=> 'AVG'
	);
}

class Report {
	
	function safeCreate($db, $name){
		if ( ! $db->hasVirtual ) Report::setup();
		$reports = MySQL::tableRecords('zReports', array('where' => "`name` = '$name'"));
		if ( empty($reports) ){
			return MySQL::tableCreateRecord('zReports', array('name'=>$name));
		}
		return $reports[0]['id'];
	}
	
	static function setup(){
		// Check if we have permissions?
		MySQL::tableCreate('zReports') or HTTP::mySQLError();
		MySQL::tableAddField('zReports', 'name', Types::$sql['Short String']);
	}
}